package com.huike.pmps.dao;

import com.huike.pmps.model.dto.cockpit.province.*;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 作者：谌贵斌(黑桃K)
 * 日期: 2888-08-08
 * 老师QQ: 272488352
 * 官方QQ交流群: 377748272
 * 刀客程序员官网：http://www.daoke360.com
 * 刀客程序员淘宝旗舰店：https://daoke360.taobao.com/
 */
public interface CockpitProvinceDao {

    @Select({
            "<script>",
            "SELECT province_name,collection_amount,payment_amount,salary,																			                    ",
            "(collection_amount-IFNULL(payment_amount,0)-IFNULL(salary,0))gross_profit,                                                                                 ",
            "ROUND((collection_amount-IFNULL(payment_amount,0)-IFNULL(salary,0))/collection_amount,2) gross_profit_rate                                                 ",
            "FROM(                                                                                                                                                      ",
            "   SELECT                                                                                                                                                  ",
            "   province_name,                                                                                                                                          ",
            "   SUM(collection_amount)  collection_amount,                                                                                                              ",
            "   (SELECT SUM(payment_amount)    FROM `p_expenses` WHERE province_id= #{provinceId} AND `year`=#{year}  AND   month_of_year=#{month})payment_amount,      ",
            "   (SELECT SUM(salary)    FROM `p_personnel_cost`      WHERE province_id= #{provinceId} AND `year`=#{year}  AND   month_of_year=#{month} )    salary       ",
            "   FROM `p_payment_collection`   WHERE province_id= #{provinceId} AND `year`=#{year}  AND   month_of_year=#{month}                                         ",
            ")temp                                                                                                                                                      ",
            "</script>"
    })
    List<OperationGrossProfitProvinceDTO> getGrossProfitByMonth(Integer provinceId, Integer year, Integer month);

    @Select({
            "<script>",
            "SELECT province_name,collection_amount,payment_amount,salary,						                                          ",
            "(collection_amount-IFNULL(payment_amount,0)-IFNULL(salary,0))gross_profit,                                                   ",
            "ROUND((collection_amount-IFNULL(payment_amount,0)-IFNULL(salary,0))/collection_amount,2) gross_profit_rate                   ",
            "FROM(                                                                                                                        ",
            "   SELECT                                                                                                                    ",
            "   province_name,                                                                                                            ",
            "   SUM(collection_amount)  collection_amount,                                                                                ",
            "   (SELECT SUM(payment_amount)    FROM `p_expenses` WHERE province_id= #{provinceId} AND `year`=#{year} )payment_amount,     ",
            "   (SELECT SUM(salary)    FROM `p_personnel_cost`      WHERE province_id= #{provinceId} AND `year`=#{year}  )    salary      ",
            "   FROM `p_payment_collection`   WHERE province_id= #{provinceId} AND `year`=#{year}                                         ",
            ")temp                                                                                                                        ",
            "</script>"
    })
    List<OperationGrossProfitProvinceDTO> getGrossProfitByYear(Integer provinceId, Integer year);


    @Select({
            "<script>",
            "SELECT 																																						",
            "province_name,                                                                                                                                                 ",
            "contract_amount,                                                                                                                                               ",
            "collection_amount,		                                                                                                                                        ",
            "(IFNULL(contract_amount,0)-IFNULL(collection_amount,0))collection_un_amount,                                                                                   ",
            "payment_amount                                                                                                                                                 ",
            "FROM(                                                                                                                                                          ",
            "	SELECT                                                                                                                                                      ",
            "	province_name,                                                                                                                                              ",
            "	SUM(contract_amount)  contract_amount,                                                                                                                      ",
            "	(SELECT SUM(collection_amount) FROM p_payment_collection  WHERE province_id= #{provinceId} AND `year`=#{year} AND month_of_year=#{month} )collection_amount,",
            "	(SELECT SUM(payment_amount)    FROM `p_expenses`          WHERE province_id= #{provinceId} AND `year`=#{year} AND month_of_year=#{month} )payment_amount    ",
            "	FROM `p_new_sign`   WHERE province_id= #{provinceId} AND `year`=#{year} AND month_of_year=#{month}                                                          ",
            ")temp                                                                                                                                                          ",
            "</script>"
    })
    List<OperationFinanceProvinceDTO> getFinanceByMonth(Integer provinceId, Integer year, Integer month);


    @Select({
            "<script>",
            "SELECT 												                                                                              ",
            "province_name,                                                                                                                       ",
            "contract_amount,                                                                                                                     ",
            "collection_amount,		                                                                                                              ",
            "(IFNULL(contract_amount,0)-IFNULL(collection_amount,0))collection_un_amount,                                                         ",
            "payment_amount                                                                                                                       ",
            "FROM(                                                                                                                                ",
            "	SELECT                                                                                                                            ",
            "	province_name,                                                                                                                    ",
            "	SUM(contract_amount)  contract_amount,                                                                                            ",
            "	(SELECT SUM(collection_amount) FROM p_payment_collection  WHERE province_id= #{provinceId} AND `year`=#{year}  )collection_amount,",
            "	(SELECT SUM(payment_amount)    FROM `p_expenses`          WHERE province_id= #{provinceId} AND `year`=#{year}  )payment_amount    ",
            "	FROM `p_new_sign`   WHERE province_id= #{provinceId} AND `year`=#{year}                                                           ",
            ")temp                                                                                                                                ",
            "</script>"
    })
    List<OperationFinanceProvinceDTO> getFinanceByYear(Integer provinceId, Integer year);


    @Select({
            "<script>",
          /*  "SELECT 														",
            "product_type_name,                                             ",
            "SUM(contract_amount)contract_amount,COUNT(id)contract_count    ",
            "FROM p_business_opportunity                                    ",
            "<where>                                                        ",
            "   <if test='provinceId!=null'>                                ",
            "     and  province_id=#{provinceId}                            ",
            "   </if>                                                       ",
            "   <if test='year!=null'>                                      ",
            "     and  year=#{year}                                         ",
            "   </if>                                                       ",
            "   <if test='month!=null'>                                     ",
            "     and  week_of_month=#{month}                               ",
            "   </if>                                                       ",
            "</where>                                                       ",
            "GROUP BY product_type_name                                     ",*/
            "  SELECT pd.product_type_name,tmp.contract_amount,tmp.contract_count		",
            "  FROM (SELECT  NAME product_type_name FROM p_dictionary WHERE TYPE=1) pd  ",
            "  LEFT JOIN                                                                ",
            "  (                                                                        ",
            "  	SELECT 													                ",
            "  	product_type_name,                                                      ",
            "  	SUM(contract_amount)contract_amount,                                    ",
            "  	COUNT(id)contract_count                                                 ",
            "  	FROM p_business_opportunity                                             ",
            "  	<where>                                                                 ",
            "  		<if test='provinceId!=null'>                                        ",
            "  			and  province_id=#{provinceId}                                  ",
            "  		</if>                                                               ",
            "  		<if test='year!=null'>                                              ",
            "  			and  year=#{year}                                               ",
            "  		</if>                                                               ",
            "  		<if test='month!=null'>                                             ",
            "  			and  week_of_month=#{month}                                     ",
            "  		</if>                                                               ",
            "  	</where>  	                                                            ",
            "  	GROUP BY product_type_name                                              ",
            "  )tmp ON pd.product_type_name=tmp.product_type_name                       ",
            "</script>"
    })
    List<BusinessOpportunityLineProvinceDTO> getBusinessOpportunities(Integer provinceId, Integer year, Integer month);



    @Select({
            "<script>",
            "SELECT CONCAT(ROUND(stage*100,0),'%')stage ,SUM(contract_amount)contract_amount  ",
            "FROM(                                                                            ",
            "	SELECT                                                                        ",
            "	province_name,                                                                ",
            "	CASE                                                                          ",
            "	WHEN stage>=0.9 THEN 0.9                                                      ",
            "	WHEN stage>=0.8 THEN 0.8                                                      ",
            "	WHEN stage>=0.7 THEN 0.7                                                      ",
            "	WHEN stage>=0.6 THEN 0.6                                                      ",
            "	WHEN stage>=0.5 THEN 0.5                                                      ",
            "	WHEN stage>=0.4 THEN 0.4                                                      ",
            "	WHEN stage>=0.3 THEN 0.3                                                      ",
            "	END stage,                                                                    ",
            "	contract_amount                                                               ",
            "	FROM p_business_opportunity                                                   ",
            "	<where>                                                                       ",
            "		stage >=0.3                                                               ",
            "		<if test='provinceId!=null'>                                              ",
            "			and  province_id=#{provinceId}                                        ",
            "		</if>                                                                     ",
            "		<if test='year!=null'>                                                    ",
            "			and  year=#{year}                                                     ",
            "		</if>                                                                     ",
            "		<if test='month!=null'>                                                   ",
            "			and  month_of_year=#{month}                                           ",
            "		</if>                                                                     ",
            "	</where>                                                                      ",
            ")temp GROUP BY stage                                                             ",
            "</script>"
    })
    List<BusinessOpportunityLine30ProvinceDTO> getBusinessOpportunitiesStage30(Integer provinceId, Integer year, Integer month);


    @Select({
            "<script>",
            " SELECT 																									  ",
            " province_name,																					          ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='未启动'   AND province_id=#{provinceId} )a,  ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='沟通中'   AND province_id=#{provinceId} )b,  ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='沟通100%' AND province_id=#{provinceId} )c,  ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='拍摄中'   AND province_id=#{provinceId} )d,  ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='拍摄100%' AND province_id=#{provinceId} )e,  ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='制作中'   AND province_id=#{provinceId} )f,  ",
            " (SELECT COUNT(id) FROM p_po_course WHERE course_schedule_name='制作100%' AND province_id=#{provinceId} )g,  ",
            " COUNT(id) total_count                                                                                       ",
            " FROM p_po_course WHERE course_schedule_name &lt;&gt; '已完结' AND  province_id=#{provinceId}                ",
            "</script>"
    })
    List<OnLineStockProvinceDTO> getOnLineCourseStock(Integer provinceId);


    @Select("select month_of_year month,round(sum(capacity)/60,2) capacity  from p_course_capacity where year=year(now()) and province_id=#{provinceId}")
    List<CourseCapacityLineProvinceDTO> getCourseCapacity(Integer provinceId);
}
